using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Text;
using UDS.Management.Data.Package;
using UDS.Management.Data.SchemaManagement;
using System.Xml.Serialization;
using System.Xml;

namespace UDS.Management.Data
{
    internal static class DBDataReplication
    {
        internal static void LoadReplicationData(Publication publication, SqlConnection Connection)
        {
            if (publication != null )
            {
                using (SqlTransaction trans = Connection.BeginTransaction())
                {
                    try
                    {
                        SqlParameter[] pubParameters = new SqlParameter[4];
                        pubParameters[0] = new SqlParameter("@Name", SqlDbType.VarChar, 255);
                        pubParameters[0].Value = publication.Name;
                        pubParameters[1] = new SqlParameter("@Version", SqlDbType.VarChar, 50);
                        pubParameters[1].Value = publication.Version;
                        pubParameters[2] = new SqlParameter("@SourceDB", SqlDbType.VarChar, 255);
                        pubParameters[2].Value = publication.SourceDB;
                        pubParameters[3] = new SqlParameter("@DestinationDB", SqlDbType.VarChar, 255);
                        pubParameters[3].Value = publication.DestinationDB;

                        Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertPublication", 20, pubParameters);
                        
                        //fill the Publication table
                        if ( publication.PublishedTable != null )
                        {
                            foreach (PublicationPublishedTable pubTable in publication.PublishedTable)
                            {
                                SqlParameter[] tableParameters = new SqlParameter[5];
                                tableParameters[0] = new SqlParameter("@PublicationVersion", SqlDbType.VarChar, 50);
                                tableParameters[0].Value = publication.Version;
                                tableParameters[1] = new SqlParameter("@TableName", SqlDbType.VarChar, 255);
                                tableParameters[1].Value = pubTable.TableName;
                                tableParameters[2] = new SqlParameter("@DestinationTableName", SqlDbType.VarChar, 255);
                                tableParameters[2].Value = pubTable.DestinationTableName;
                                tableParameters[3] = new SqlParameter("@Operation", SqlDbType.VarChar, 255);
                                tableParameters[3].Value = pubTable.Operation;
                                tableParameters[4] = new SqlParameter("@schema_option", SqlDbType.VarChar, 255);
                                tableParameters[4].Value = pubTable.schemas_option;

                                Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertPublishedTable", 20, tableParameters);

                                SqlParameter[] tableDelParameters = new SqlParameter[2];
                                tableDelParameters[0] = new SqlParameter("@PublicationVersion", SqlDbType.VarChar, 50);
                                tableDelParameters[0].Value = publication.Version;
                                tableDelParameters[1] = new SqlParameter("@TableName", SqlDbType.VarChar, 255);
                                tableDelParameters[1].Value = pubTable.TableName;

                                Execute.ExecuteStoredProcedure(trans, "sproc_repl_DeleteTableArticleColumns", 20, tableDelParameters);

                                if (pubTable.ArticleColumn != null)
                                {
                                    foreach (PublicationPublishedTableArticleColumn artcolumn in pubTable.ArticleColumn)
                                    {
                                        SqlParameter[] columnParameters = new SqlParameter[10];
                                        columnParameters[0] = new SqlParameter("@PublicationVersion", SqlDbType.VarChar, 50);
                                        columnParameters[0].Value = publication.Version;
                                        columnParameters[1] = new SqlParameter("@TableName", SqlDbType.VarChar, 255);
                                        columnParameters[1].Value = pubTable.TableName;
                                        columnParameters[2] = new SqlParameter("@ColumnName", SqlDbType.VarChar, 255);
                                        columnParameters[2].Value = artcolumn.ColumnName;
                                        columnParameters[3] = new SqlParameter("@ColumnOrder", SqlDbType.Int);
                                        columnParameters[3].Value = artcolumn.ColumnOrder;
                                        columnParameters[4] = new SqlParameter("@DestinationColumnName", SqlDbType.VarChar, 255);
                                        columnParameters[4].Value = artcolumn.DestinationColumnName;
                                        columnParameters[5] = new SqlParameter("@SourceDataType", SqlDbType.VarChar, 255);
                                        columnParameters[5].Value = artcolumn.SourceDataType.ToString();
                                        columnParameters[6] = new SqlParameter("@SourceDataWidth", SqlDbType.Int);
                                        columnParameters[6].Value = artcolumn.SourceDataWidth;
                                        columnParameters[7] = new SqlParameter("@DestinationDataType", SqlDbType.VarChar, 255);
                                        columnParameters[7].Value = artcolumn.DestinationDataType.ToString();
                                        columnParameters[8] = new SqlParameter("@DestinationWidth", SqlDbType.Int);
                                        columnParameters[8].Value = artcolumn.DestinationWidth;
                                        columnParameters[9] = new SqlParameter("@IsPartOfPK", SqlDbType.Int);
                                        columnParameters[9].Value = artcolumn.IsPartOfPK;

                                        Execute.ExecuteStoredProcedure(trans, "sproc_repl_UpsertArticleColumn", 20, columnParameters);
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception)
                    {
                        try
                        {
                            trans.Rollback();
                        }
                        catch
                        {
                            // Ignore
                        }
                        throw;
                    }
                    trans.Commit();
                }
            }
        }
    }
}
